package softswitch.ws;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;

import javax.ejb.EJB;
import javax.ejb.Stateless;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import pbx.model.Cdr;
import pbx.ws.PbxWS;
import security.model.Corporation;
import security.model.Organization;
import security.service.SecurityService;
import softswitch.model.Country;
import softswitch.model.Did;
import softswitch.model.Operator;
import softswitch.model.Provider;
import softswitch.model.Route;
import softswitch.model.RouteGroup;
import softswitch.model.RoutePrefix;
import softswitch.service.CountryService;
import softswitch.service.ProviderService;
import softswitch.service.RouteService;



@Stateless
public class UploadService {

    @EJB
    SoftswitchService softswitchService;

    @EJB
    SecurityService securityService;

    @EJB
    CountryService countryService;
    
    @EJB
    ProviderService providerService;
    
    @EJB
    RouteService routeService;
    
    @EJB
    PbxWS pbxService;


    public Integer uploadRoute(XSSFWorkbook book) {
        int out = 0;
        int empty = 0;
        int processed = 0;
        int passed = 0;
        int numSheets = book.getNumberOfSheets();

        XSSFWorkbook wb = (XSSFWorkbook) book;

        System.out.println("TOTAL SHEETS: " + numSheets);

        // READ SHEETS
        for (int i = 0; i < 1; i++) {
            System.out.println("READING SHEET NUMBER: " + i);
            XSSFSheet sheet = wb.getSheetAt(i);
            int numRows = sheet.getLastRowNum();

            System.out.println("#Total Rows: " + numRows);
            System.out.println("-------------------------------------------------------------------");
            System.out.println(" ");
            // READ ROWS
            for (int x = 1; x <= numRows; x++) {
                Row row = sheet.getRow(x);
                System.out.println("Reading Row Number: " + x);

                // MANAGE COUNTRY
                String countryisocode = "";
                int routegroupcode = 0;
                int operatorcode = 0;
                String name = "";
                int prefix = 0;

                try {
                    countryisocode = row.getCell(0).getStringCellValue().trim();
                    routegroupcode = (int) row.getCell(1).getNumericCellValue();
                    operatorcode = (int) row.getCell(2).getNumericCellValue();
                    name = row.getCell(3).getStringCellValue().trim();
                    prefix = (int) row.getCell(4).getNumericCellValue();
                } catch (Exception e) {
                    System.out.println("WARNING: EXCEPTION --> " + e.getMessage());
                }
                //VALIDATING NULLS OR EMPTY
                if (countryisocode.equalsIgnoreCase("") || name.equalsIgnoreCase("") || routegroupcode == 0 || operatorcode == 0 || prefix == 0) {
                    empty++;
                    System.out.println("ERROR: NOT PROCESSED, EMPTY OR INCORRECT COLUMN VALUE ");
                    if (countryisocode.equalsIgnoreCase("")) {
                        System.out.println("** Caused By: countryisocode is EMPTY");
                    } else {
                        System.out.println("countryisocode: " + countryisocode);
                    }

                    if (name.equalsIgnoreCase("")) {
                        System.out.println("** Caused By: name is EMPTY");
                    } else {
                        System.out.println("name: " + name);
                    }

                    if (routegroupcode == 0) {
                        System.out.println("** Caused By: routegroupcode is EMPTY");
                    } else {
                        System.out.println("routegroupcode: " + routegroupcode);
                    }

                    if (operatorcode == 0) {
                        System.out.println("** Caused By: operatorcode is EMPTY");
                    } else {
                        System.out.println("operatorcode: " + operatorcode);
                    }

                    if (prefix == 0) {
                        System.out.println("** Caused By: prefix is EMPTY");
                    } else {
                        System.out.println("prefix: " + prefix);
                    }

                } else {

                    //VALIDATING COUNTRY
                    Country country = countryService.getByIsoCode(countryisocode);
                    Operator operator = softswitchService.getOperatorByCode(operatorcode);
                    RouteGroup routegroup = softswitchService.getRouteGroupByCode(routegroupcode);

                    if (country == null || routegroup == null || operator == null) {
                        if (country == null) {
                            System.out.println("WARNING: Country is null");
                        }
                        if (routegroup == null) {
                            System.out.println("WARNING: Routegroup is null");
                        }
                        if (operator == null) {
                            System.out.println("WARNING: Operator is null");
                        }
                        passed++;
                    } else {

                        System.out.println("CHECKING: Route");
                        Route route = routeService.getByNameCountryRouteGrupOperator(name, country, routegroup, operator);
                        if (route == null) {
                            route = new Route();
                            route.setCountry(country);
                            route.setRoutegroup(routegroup);
                            route.setOperator(operator);
                            route.setName(name);
                            routeService.save(route);
                            System.out.println("SAVE ROUTE: " + name);
                        } else {
                            System.out.println("ROUTE " + route.getName() + " - " + operator.getAlias() + " ALREADY EXISTS");
                        }

                        //PREFIX
                        System.out.println("CHECKING: Prefix " + prefix);
                        RoutePrefix routeprefix = softswitchService.getRoutePrefixByPrefix(prefix);

                        if (routeprefix == null) {
                            routeprefix = new RoutePrefix();
                            routeprefix.setPrefix(prefix);
                            routeprefix.setRoute(route);
                            softswitchService.saveRoutePrefix(routeprefix);
                            System.out.println("SAVE PREFIX: " + prefix);
                        } else {
                            System.out.println("WARNING: PREFIX ALREADY EXISTS --> " + prefix);
                            System.out.println("... CHECKING PREFIX - ROUTE MATCH ...");
                            if (routeprefix.getRoute().getId().intValue() == route.getId()) {
                                System.out.println("MATCH : Yes");
                            } else {
                                System.out.println("ERROR: No MATCH , Please check row: " + row + "for inconsistence data.");
                            }
                        }
                        processed++;
                    }
                }
                System.out.println("-------------------------------------------------------------------");
                System.out.println(" ");
            }

            out = 1;
            System.out.println("***************** SUMMARY *******************************************");
            System.out.println("TOTAL ROWS : " + numRows + "rows");
            System.out.println("PROCESSED : " + processed + "rows");
            System.out.println("EMPTY : " + empty + "rows");
            System.out.println("NOT INSERTED : " + passed + "rows");
            System.out.println(" ");

        }
        return out;
    }

    public Integer uploadPlan(XSSFWorkbook book) {
        return 0;
    }

    public Integer uploadCDR(XSSFWorkbook book) {

        XSSFSheet sheet = book.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {

            Row row = rowIterator.next();
            Cell cell0 = row.getCell(0);

            String calldate = null;
            if (cell0.getCellType() == 0) {
                calldate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell0.getDateCellValue());
            }
            if (cell0.getCellType() == 1) {
                cell0.setCellType(Cell.CELL_TYPE_STRING);
                calldate = cell0.getStringCellValue();
            }

            Cell cell1 = row.getCell(1);
            cell1.setCellType(Cell.CELL_TYPE_STRING);
            String accountcode = cell1.getStringCellValue();

            Cell cell2 = row.getCell(2);
            cell2.setCellType(Cell.CELL_TYPE_STRING);
            String src = cell2.getStringCellValue();

            Cell cell3 = row.getCell(3);
            cell3.setCellType(Cell.CELL_TYPE_STRING);
            String destination = cell3.getStringCellValue();

            Cell cell4 = row.getCell(4);
            Double time = cell4.getNumericCellValue();

            Cdr cdr = new Cdr();

            cdr.setClid(src);
            cdr.setSrc(src);
            cdr.setDst(destination);
            if (accountcode.length() > 4) {
                Did did = softswitchService.getDidByNumber(Integer.parseInt(accountcode));
                if (did == null) {
                    System.out.println(accountcode + " Not Found");
                } else {
                    accountcode = String.valueOf(did.getCircuit().getCode());
                    System.out.println("PROCESSING " + accountcode + " "
                            + calldate + " " + time);
                }
            }
            cdr.setAccountcode(accountcode);
            cdr.setBillsec(time);
            cdr.setDisposition("ANSWERED");
            cdr.setDcontext("outbound");

            // //////////////
            Date date = null;
            try {
                date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(calldate);
            } catch (ParseException e1) {
                e1.printStackTrace();
            }
            Integer sum = 0;
            for (int i = 0; i < destination.length(); i++) {
                sum = sum + Integer.parseInt(destination.substring(i, i + 1));
            }
            cdr.setUniqueid(date.getTime() + "." + time + sum);

            // cdr.setVersion(null);
            cdr.setAnswer(date);
            cdr.setBilled(null);

            try {
                pbxService.saveCdr(cdr);
            } catch (Exception e) {
                e.printStackTrace();
                System.out.println("ERROR");
            }
        }
        return 0;

    }

    public Integer uploadCorporation(XSSFWorkbook book) {

        int out = 0;
        XSSFWorkbook wb = (XSSFWorkbook) book;
        int numSheets = book.getNumberOfSheets();
        System.out.println("Sheets Total: " + numSheets);

        // READ SHEETS
        for (int i = 0; i < 1; i++) {
            System.out.println("Reading Sheet Number: " + i);

            XSSFSheet sheet = wb.getSheetAt(i);
            int numRows = sheet.getLastRowNum();
            System.out.println("# Rows: " + numRows);

            // READ ROWS
            for (int x = 3; x <= 349; x++) {
                Row row = sheet.getRow(x);
                System.out.println("Reading Row Number: " + x);

                // MANAGE CORPORATION
                System.out.println("Row:" + x + "MANAGE CORPORATION");
                String fullname = row.getCell(1).getStringCellValue().trim();
                String alias = row.getCell(2).getStringCellValue().trim();

                int idCorporation = Integer.parseInt(fullname.substring(6)
                        .trim());

                Corporation corporation = securityService
                        .getCorporationById(idCorporation);
                if (corporation == null) {
                    System.out.println("CREATE CORPORATION");
                    corporation = new Corporation();
                    corporation.setId(idCorporation);
                    corporation.setName(fullname);
                    securityService.saveCorporation(corporation);
                    System.out.println("SAVE CORPORATION: " + fullname);
                }

                Organization organization = securityService
                        .getOrganizationByAlias(alias);
                if (organization == null) {
                    System.out.println("ALIAS NOT FOUND: " + alias);
                } else {
                    organization.setCorporation(corporation);
                    securityService.updateOrganization(organization);
                }

                System.out
                        .println("-------------------------------------------------------------------");
            }
            out = 1;
        }
        return out;

    }// uploadCDR()

    
   
    
    public Integer uploadOperator(XSSFWorkbook book) {
        int out = 0;
        int empty = 0;
        int processed = 0;
        int passed = 0;
        int numSheets = book.getNumberOfSheets();

        XSSFWorkbook wb = (XSSFWorkbook) book;

        System.out.println("TOTAL SHEETS: " + numSheets);

        // READ SHEETS
        for (int i = 0; i < 1; i++) {
            System.out.println("READING SHEET NUMBER: " + i);
            XSSFSheet sheet = wb.getSheetAt(i);
            int numRows = sheet.getLastRowNum();

            System.out.println("#Total Rows: " + numRows);
            System.out.println("-------------------------------------------------------------------");
            System.out.println(" ");
            // READ ROWS
            for (int x = 1; x <= numRows; x++) {
                Row row = sheet.getRow(x);
                System.out.println("Reading Row Number: " + x);

                String name = "";
                String alias = "";
                String description = "";
                int code = 0;
                String countryisocode = "";
                // MANAGE OPERATOR
                try {                   
                    name = row.getCell(0).getStringCellValue().trim();
                    alias = row.getCell(1).getStringCellValue().trim();
                    description = row.getCell(2).getStringCellValue().trim();
                    code = (int) row.getCell(3).getNumericCellValue();
                    countryisocode = row.getCell(4).getStringCellValue().trim();
                } catch (Exception e) {
                    System.out.println("WARNING: EXCEPTION: " + e.getMessage());
                }
                //VALIDATING NULLS OR EMPTY
                if ( name.equalsIgnoreCase("") || alias.equalsIgnoreCase("") || code == 0 || countryisocode.equalsIgnoreCase("")) {
                    empty++;
                    System.out.println("NOT PROCESSED, EMPTY OR INCORRECT COLUMN VALUE ");
                } else {
                    Operator operator = softswitchService.getOperatorByCode(code);
                    Country country = countryService.getByIsoCode(countryisocode);

                    if (operator == null) {
                        System.out.println("CREATE OPERATOR");
                        operator = new Operator();
                        operator.setAlias(alias);
                        operator.setDescription(description);
                        operator.setName(name);
                        operator.setCode(code);
                        operator.setCountry(country);
                        softswitchService.saveOperator(operator);
                        processed++;
                        System.out.println("SAVE OPERATOR: " + name);
                    } else {
                        System.out.println("ROW " + x + "OPERATOR ALREADY EXISTS");
                        passed++;
                    }
                }
                System.out.println("-------------------------------------------------------------------");
                System.out.println(" ");
            }

            out = 1;
            System.out.println("***************** SUMMARY *******************************************");
            System.out.println("TOTAL ROWS : " + numRows + "rows");
            System.out.println("PROCESSED : " + processed + "rows");
            System.out.println("EMPTY : " + empty + "rows");
            System.out.println("PASSED : " + passed + "rows");
            System.out.println(" ");

        }
        return out;
    }

    public Integer uploadProvider(XSSFWorkbook book) {
        int out = 0;
        int empty = 0;
        int processed = 0;
        int passed = 0;
        int numSheets = book.getNumberOfSheets();

        XSSFWorkbook wb = (XSSFWorkbook) book;

        System.out.println("TOTAL SHEETS: " + numSheets);

        // READ SHEETS
        for (int i = 0; i < 1; i++) {
            System.out.println("READING SHEET NUMBER: " + i);
            XSSFSheet sheet = wb.getSheetAt(i);
            int numRows = sheet.getLastRowNum();

            System.out.println("#Total Rows: " + numRows);
            System.out.println("-------------------------------------------------------------------");
            System.out.println(" ");
            // READ ROWS
            for (int x = 1; x <= numRows; x++) {
                Row row = sheet.getRow(x);
                System.out.println("Reading Row Number: " + x);

                String name = "";
                String alias = "";
                String description = "";
                int code = 0;
                String countryisocode = "";
                // MANAGE OPERATOR
                try {                   
                    name = row.getCell(0).getStringCellValue().trim();
                    alias = row.getCell(1).getStringCellValue().trim();
                    description = row.getCell(2).getStringCellValue().trim();
                    code = (int) row.getCell(3).getNumericCellValue();
                    countryisocode = row.getCell(4).getStringCellValue().trim();
                }
                catch (Exception e) {
                    System.out.println("WARNING: EXCEPTION: " + e.getMessage());
                }
                //VALIDATING NULLS OR EMPTY
                if ( name.equalsIgnoreCase("") || alias.equalsIgnoreCase("") || code == 0 || countryisocode.equalsIgnoreCase("")) {
                    empty++;
                    System.out.println("NOT PROCESSED, EMPTY OR INCORRECT COLUMN VALUE ");
                }
                else {
                    Provider provider = providerService.getByCode(code);
                    Country country = countryService.getByIsoCode(countryisocode);

                    if (provider == null) {
                        System.out.println("CREATE OPERATOR");
                        provider = new Provider();
                        provider.setAlias(alias);
                        provider.setDescription(description);
                        provider.setName(name);
                        provider.setCode(code);
                        provider.setCountry(country);
                        providerService.save(provider);
                        processed++;
                        System.out.println("SAVE OPERATOR: " + name);
                    } else {
                        System.out.println("ROW " + x + "OPERATOR ALREADY EXISTS");
                        passed++;
                    }
                }
                System.out.println("-------------------------------------------------------------------");
                System.out.println(" ");
            }

            out = 1;
            System.out.println("***************** SUMMARY *******************************************");
            System.out.println("TOTAL ROWS : " + numRows + "rows");
            System.out.println("PROCESSED : " + processed + "rows");
            System.out.println("EMPTY : " + empty + "rows");
            System.out.println("PASSED : " + passed + "rows");
            System.out.println(" ");

        }
        return out;
    }
    
}
